using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class TVC_CATEDataAccess : Connection
    {
        #region Local Variable

        #endregion Local Variable

        #region Method
        public TVC_CATEInfo Get(string db,
        String maingroup,
        String subgroup,
        String sizecode,
        String lengthcode,
        ref string sErr)
        {
            TVC_CATEInfo objEntr = new TVC_CATEInfo();

            InitConnect();
            InitCommand(@"SELECT     maingroup, mainname, subgroup, subname, sizecode, sizename, lengthcode, lengthname
                FROM         TVC_CATE" + db + @"
                WHERE     (maingroup = @maingroup) AND (subgroup = @subgroup) AND (sizecode = @sizecode) AND (lengthcode = @lengthcode)");

            AddParameter(TVC_CATEInfo.Field.maingroup, maingroup);
            AddParameter(TVC_CATEInfo.Field.subgroup, subgroup);
            AddParameter(TVC_CATEInfo.Field.sizecode, sizecode);
            AddParameter(TVC_CATEInfo.Field.lengthcode, lengthcode);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            if (list.Rows.Count > 0)
                objEntr = new TVC_CATEInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable GetAll(string db, ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT     Code, Description, Lookup, Status, maingroup, mainname, subgroup, subname, sizecode, sizename, lengthcode, lengthname, rowrank
FROM         (SELECT     maingroup + subgroup AS Code, mainname + ' - ' + subname AS Description, '' AS Lookup, '' AS Status, maingroup, mainname, subgroup, subname, 
                                              sizecode, sizename, lengthcode, lengthname, rowrank
                       FROM          TVC_CATE" + db + @") cate 
ORDER BY maingroup, subgroup, rowrank", CommandType.Text);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(TVC_CATEInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitCommand(@"INSERT INTO TVC_CATE" + objEntr.db + @"
                      (maingroup, mainname,rowrank, subgroup, subname, sizecode, sizename, lengthcode, lengthname)
                VALUES     (@maingroup,@mainname,@rowrank, @subgroup,@subname,@sizecode,@sizename,@lengthcode,@lengthname)");
            AddParameter(TVC_CATEInfo.Field.maingroup, objEntr.maingroup);
            AddParameter(TVC_CATEInfo.Field.mainname, objEntr.mainname);
            AddParameter(TVC_CATEInfo.Field.rowrank, objEntr.rowrank);
            AddParameter(TVC_CATEInfo.Field.subgroup, objEntr.subgroup);
            AddParameter(TVC_CATEInfo.Field.subname, objEntr.subname);
            AddParameter(TVC_CATEInfo.Field.sizecode, objEntr.sizecode);
            AddParameter(TVC_CATEInfo.Field.sizename, objEntr.sizename);
            AddParameter(TVC_CATEInfo.Field.lengthcode, objEntr.lengthcode);
            AddParameter(TVC_CATEInfo.Field.lengthname, objEntr.lengthname);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(TVC_CATEInfo objEntr)
        {
            openConnection();
            InitCommand(@"UPDATE    TVC_CATE" + objEntr.db + @"
                    SET mainname = @mainname, subname = @subname, sizename = @sizename, lengthname = @lengthname
                WHERE     (maingroup = @maingroup) AND (subgroup = @subgroup) AND (sizecode = @sizecode) AND (lengthcode = @lengthcode)");

            AddParameter(TVC_CATEInfo.Field.maingroup, objEntr.maingroup);
            AddParameter(TVC_CATEInfo.Field.mainname, objEntr.mainname);
            AddParameter(TVC_CATEInfo.Field.subgroup, objEntr.subgroup);
            AddParameter(TVC_CATEInfo.Field.subname, objEntr.subname);
            AddParameter(TVC_CATEInfo.Field.sizecode, objEntr.sizecode);
            AddParameter(TVC_CATEInfo.Field.sizename, objEntr.sizename);
            AddParameter(TVC_CATEInfo.Field.lengthcode, objEntr.lengthcode);
            AddParameter(TVC_CATEInfo.Field.lengthname, objEntr.lengthname);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(string db,
        String maingroup,
        String subgroup = null,
        String sizecode = null,
        String lengthcode = null
        )
        {
            openConnection();
            string sql = @"DELETE FROM TVC_CATE" + db + @"
                WHERE     (maingroup = @maingroup)";
            if (subgroup != null) sql += " AND (subgroup = @subgroup)";
            if (sizecode != null) sql += " AND (sizecode = @sizecode)";
            if (lengthcode != null) sql += " AND (lengthcode = @lengthcode)";
            InitCommand(sql);

            AddParameter(TVC_CATEInfo.Field.maingroup, maingroup);
            if (subgroup != null) AddParameter(TVC_CATEInfo.Field.subgroup, subgroup);
            if (sizecode != null) AddParameter(TVC_CATEInfo.Field.sizecode, sizecode);
            if (lengthcode != null) AddParameter(TVC_CATEInfo.Field.lengthcode, lengthcode);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public Boolean IsExist(string db,
        String maingroup,
        String subgroup,
        String sizecode,
        String lengthcode
        )
        {
            InitConnect();
            InitCommand(@"SELECT  TOP(1)  maingroup
                FROM         TVC_CATE" + db + @"
                WHERE     (maingroup = @maingroup) AND (subgroup = @subgroup) AND (sizecode = @sizecode) AND (lengthcode = @lengthcode)");
            AddParameter(TVC_CATEInfo.Field.maingroup, maingroup);
            AddParameter(TVC_CATEInfo.Field.subgroup, subgroup);
            AddParameter(TVC_CATEInfo.Field.sizecode, sizecode);
            AddParameter(TVC_CATEInfo.Field.lengthcode, lengthcode);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }


        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            return GetAll(dtb, ref sErr);
        }
        #endregion Method


        public DataTable GetbyFilter(string db, string strFilter, ref string sErr)
        {
            string sql = @"SELECT     Code, Description, Lookup, Status, maingroup, mainname, subgroup, subname, sizecode, sizename, lengthcode, lengthname, rowrank
FROM         (SELECT     maingroup + subgroup AS Code, mainname + ' - ' + subname AS Description, '' AS Lookup, '' AS Status, maingroup, mainname, subgroup, subname, 
                                              sizecode, sizename, lengthcode, lengthname, rowrank
                       FROM          TVC_CATE" + db + @") cate ";

            InitConnect();
            if (strFilter != "") sql += " WHERE " + strFilter;
            sql += " ORDER BY maingroup, subgroup, rowrank";
            InitCommand(sql);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
    }
}
